﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace OlapProject
{
    class RDB
    {
        private SqlConnection connection;
        private string tempTable;
        

        public RDB(SqlConnection connection, string tempTable)
        {
            this.connection = connection;
            if(this.connection.State != ConnectionState.Open)
                this.connection.Open();
            this.tempTable = tempTable;
        }

        private string escape(string value)
        {
            return value.Replace("'", "''");
        }

        private int getTableKeyId(string tableName, string name)
        {
            
            string query = "SELECT id FROM " + tableName + " WHERE name = '" + escape(name) + "'; SELECT SCOPE_IDENTITY();";
            SqlCommand cmd = new SqlCommand(query, connection);
            SqlDataReader mySqlDataReader = cmd.ExecuteReader();
            bool found = mySqlDataReader.Read();

            if (found)
            {
                int id = (int)mySqlDataReader["id"];
                mySqlDataReader.Close();
                return id;
            }
            else
            {
                mySqlDataReader.Close();

                query = "INSERT INTO " + tableName + " (name) VALUES ('" + escape(name) + "'); SELECT SCOPE_IDENTITY();";
                cmd = new SqlCommand(query, connection);
                decimal lastId = (decimal)cmd.ExecuteScalar();
                 
                return (int)lastId;
            }
        }

        public void removeTrash()
        {
            string query =
                "DELETE FROM " + tempTable + " WHERE source = '';" +
                "DELETE FROM " + tempTable + " WHERE datetime = '';" +
                "DELETE FROM " + tempTable + " WHERE type = '';" +
                "DELETE FROM " + tempTable + " WHERE price < 100;" +
                "DELETE FROM " + tempTable + " WHERE country = '';" +
                "DELETE FROM " + tempTable + " WHERE country NOT IN ('spain', 'bulgaria', 'greece', 'portugal', 'italy', 'turkey', 'france', 'hungary', 'croatia', 'romania', 'poland', 'cyprus', 'czech republic', 'latvia', 'slovenia', 'slovakia', 'malta', 'germany', 'ukraine', 'lithuania', 'yugoslavia', 'montenegro', 'albania', 'austria', 'russia', 'sweden', 'macedonia', 'belgium', 'estonia', 'switzerland', 'bosnia and herzegovina', 'netherlands', 'finland', 'moldova', 'serbia', 'ireland', 'norway', 'united kingdom', 'andorra', 'luxembourg', 'iceland', 'denmark', 'belarus', 'san marino', 'liechtenstein', 'gibraltar');" +
                "UPDATE " + tempTable + " SET city = '' WHERE city LIKE '%[0-9]%' or LEN(city) > 30 or city = '-';";

            SqlCommand cmd = new SqlCommand(query, connection);
            cmd.CommandTimeout = 600;
            cmd.ExecuteNonQuery();
        }

        public void truncate()
        {
            string query = "TRUNCATE TABLE " + tempTable + ";";
            SqlCommand cmd = new SqlCommand(query, connection);
            cmd.CommandTimeout = 600;
            cmd.ExecuteNonQuery();
        }

        private void insertRdbRows(List<Hashtable> data)
        {
            foreach (Hashtable row in data)
            {
                int countryId = getTableKeyId("country", (string)row["country"]);
                int cityId = getTableKeyId("city", (string)row["city"]);
                int typeId = getTableKeyId("type", (string)row["type"]);
                int sourceId = getTableKeyId("source", (string)row["source"]);

                string insertQuery = "INSERT INTO property(" +
                    "district, price, area, plotArea, bedrooms, bathrooms, datetime, id_country, id_city, id_type, id_source) VALUES(" +
                   "@district, @price, @area, @plotArea, @bedrooms, @bathrooms, @datetime, @id_country, @id_city, @id_type, @id_source" +
               ")";

                SqlCommand insertCmd = new SqlCommand(insertQuery, connection);

                insertCmd.Parameters.AddWithValue("@district", escape((string)row["district"]));
                insertCmd.Parameters.AddWithValue("@price", row["price"]);
                insertCmd.Parameters.AddWithValue("@area", row["area"]);
                insertCmd.Parameters.AddWithValue("@plotArea", row["plotArea"]);
                insertCmd.Parameters.AddWithValue("@bedrooms", row["bedrooms"]);
                insertCmd.Parameters.AddWithValue("@bathrooms", row["bathrooms"]);
                insertCmd.Parameters.AddWithValue("@datetime", row["datetime"]);
                insertCmd.Parameters.AddWithValue("@id_country", countryId);
                insertCmd.Parameters.AddWithValue("@id_city", cityId);
                insertCmd.Parameters.AddWithValue("@id_type", typeId);
                insertCmd.Parameters.AddWithValue("@id_source", sourceId);

                insertCmd.ExecuteNonQuery();
            }
        }

        public void move()
        {
            string query = "select convert(int, rows) as number from sysindexes where id = OBJECT_ID('" + tempTable + "') and indid < 2 ";

            SqlCommand cmd = new SqlCommand(query, connection);
            cmd.CommandTimeout = 600;
            SqlDataReader mySqlDataReader = cmd.ExecuteReader();

            mySqlDataReader.Read();
            int rows = (int)mySqlDataReader["number"];
            mySqlDataReader.Close();

            int rowsPerStep = 1000;

            while (rows > 0)
            {
                
                query = "SELECT TOP " + rowsPerStep + " * FROM " + tempTable + "";
                cmd = new SqlCommand(query, connection);
                cmd.CommandTimeout = 600;
                mySqlDataReader = cmd.ExecuteReader();


                rows -= rowsPerStep;

                if (rows < rowsPerStep && rows > 0) { rows = rowsPerStep; }

                List<Hashtable> data = new List<Hashtable>();
               
                while (mySqlDataReader.Read())
                {

                    Hashtable row = new Hashtable()
                    {        
                        {"country", (string) mySqlDataReader["country"]},
                        {"city", (string) mySqlDataReader["city"]},
                        {"type", (string) mySqlDataReader["type"]},
                        {"source", (string) mySqlDataReader["source"]},
                        {"district", (string) mySqlDataReader["district"]},
                        {"price", (decimal) mySqlDataReader["price"]},
                        {"area", (decimal) mySqlDataReader["area"]},
                        {"plotArea", (decimal) mySqlDataReader["plotArea"]},
                        {"bedrooms", (int) mySqlDataReader["bedrooms"]},
                        {"bathrooms", (int) mySqlDataReader["bathrooms"]},
                        {"datetime", (DateTime) mySqlDataReader["datetime"]}
                    };

                    data.Add(row);
                }

                mySqlDataReader.Close();

                query = "delete t1 from (SELECT TOP " + rowsPerStep + " * FROM " + tempTable + ") t1";
                cmd = new SqlCommand(query, connection);
                cmd.ExecuteNonQuery();

                insertRdbRows(data);
            }
        }
    }
}
